package com.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import javax.naming.NamingException;

import com.domain.Treatments;

public class TreatmentsDAO extends BaseDAO {
	public int insert(Treatments treatment) {
		int result = 0;
		try {
			Connection conn = getConnection();
			String sql = "insert into treatments values(?, ?, ?, ?, ?, ?)";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, treatment.getTreatmentCode());
			stmt.setString(2, treatment.getTreatmentName());
			stmt.setFloat(3, treatment.getSalesPrice());
			stmt.setString(4, treatment.getDescription());
			stmt.setDate(5, new Date(new java.util.Date().getTime()));
			stmt.setInt(6, treatment.getVersion());
			result = stmt.executeUpdate();
			conn.close();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public int update(Treatments treatment) {
		int result = 0;
		try {
			Connection conn = getConnection();
			String sql = "update treatments set salesPrice=? where treatmentCode=?;";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setDouble(1, treatment.getSalesPrice());
			stmt.setString(2, treatment.getTreatmentCode());
			result = stmt.executeUpdate();
			conn.close();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	public List<Treatments> getAllTreatments() {
		List<Treatments> list = new LinkedList<Treatments>();
		try {
			Connection conn = getConnection();
			String sql = "SELECT * FROM treatments WHERE (treatmentcode, version) IN (SELECT treatmentcode, MAX( version ) AS version FROM treatments GROUP BY treatmentcode)";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String treatmentCode = rs.getString("treatmentCode");
				String treatmentName = rs.getString("treatmentName");
				float salesPrice = rs.getFloat("salesPrice");
				String description = rs.getString("description");
				Date date = rs.getDate("modDate");
				int version = rs.getInt("version");
				list.add(new Treatments(treatmentCode, treatmentName, salesPrice,
						description, date, version));
			}
			conn.close();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public Treatments getTreatmentByCode(String code) {
		Treatments p = null;
		try {
			Connection conn = getConnection();
			String sql = "SELECT * FROM treatments WHERE treatmentcode=? and (treatmentcode, version) IN (SELECT treatmentcode, MAX( version ) AS version FROM treatments GROUP BY treatmentcode)";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, code);
			ResultSet rs = stmt.executeQuery();
			rs.next();
			if (!rs.wasNull()) {
				String treatmentCode = rs.getString("treatmentCode");
				String treatmentName = rs.getString("treatmentName");
				float salesPrice = rs.getFloat("salesPrice");
				String description = rs.getString("description");
				Date date = rs.getDate("modDate");
				int version = rs.getInt("version");
				p = new Treatments(treatmentCode, treatmentName, salesPrice,
						description, date, version);
			}
			conn.close();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return p;
	}

	public void deleteByTreatmentCode(String code) {
		try {
			Connection conn = getConnection();
			String sql = "delete from treatments where treatmentCode=?";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, code);
			stmt.executeUpdate();
			conn.close();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
